#!/usr/bin/env python
# -*- coding:utf8 -*-

"""
@version: 
@author: lh
@license: Apache Licence 
@contact: liuhuan0672@gmail.com
@site: 
@software: PyCharm
@file: 0002.py.py
@time: 2016/1/20 10:48

第 0002 题：将 0001 题生成的 200 个激活码（或者优惠券）保存到 MySQL 关系型数据库中。
"""
import uuid
import pymysql.cursors


def generate_key():
    key_list = []
    for i in range(200):
        uuid_key = uuid.uuid3(uuid.NAMESPACE_DNS, str(uuid.uuid1()))
        key_list.append(str(uuid_key).replace('-', ''))
    return key_list


def write_to_mysql(key_list):
    connection = pymysql.connect(host='localhost',
                                 user='root',
                                 password='admin123',
                                 db='pydb',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    try:
        with connection.cursor() as cursor:
            sql = 'drop table if exists u_keys'
            cursor.execute(sql)

            sql = "create table u_keys (key_value char(40) not null)"
            cursor.execute(sql)

            # connection is not autocommit by default. So you must commit to save
            # your changes.
        connection.commit()

        with connection.cursor() as cursor:
            for i in range(200):
                cursor.execute('insert into u_keys VALUES("%s")' % (key_list[i]))
        connection.commit()
    finally:
        connection.close()


if __name__ == '__main__':
    write_to_mysql(generate_key())
